package dtu.app.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import dtu.app.model.Product;

public class ProductDatabaseHelper {

	private static final String TAG = "ProductDatabaseHelper.class";

	// database configuration
	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "fridgeapp.db";

	// table configuration
	private static final String TABLE_NAME = "products";
	private static final String TABLE_NAME_TEMP = "products_temp";
	private static final String PRODUCT_ID = "_id"; // _id is required for the cursor
	private static final String PRODUCT_NAME = "product_name";
	private static final String PRODUCT_CATEGORY = "product_category";
	private static final String PRODUCT_UNIT = "product_unit";
	private static final String PRODUCT_PIECES = "product_pieces";
	private static final String PRODUCT_TOTAL_PIECES = "product_total_pieces";
	private static final String PRODUCT_PIECES_AMOUNT = "product_pieces_amount";
	private static final String PRODUCT_TOTAL_AMOUNT = "product_total_amount";
	private static final String PRODUCT_IN_STOCK_PIECES = "product_in_stock_pieces";
	private static final String PRODUCT_IN_STOCK_AMOUNT = "product_in_stock_amount";
	private static final String PRODUCT_IMAGE_PATH = "product_image_path";
		
	private DatabaseOpenHelper openHelper;
	private SQLiteDatabase database;
	
	// this is a wrapper class. that means, from outside world, anyone will communicate with ProductDatabaseHelper,
	// but under the hood actually DatabaseOpenHelper class will perform database CRUD operations 
	public ProductDatabaseHelper(Context aContext) {
		openHelper = new DatabaseOpenHelper(aContext);
		database = openHelper.getWritableDatabase();
	}

	public void insertProduct(Product product, boolean tempList) {
		// We are using ContentValues to avoid sql format errors
		ContentValues contentValues = new ContentValues();
		contentValues.put(PRODUCT_NAME, product.getName());
		contentValues.put(PRODUCT_CATEGORY, product.getCategory().toUpperCase(Locale.US));
		contentValues.put(PRODUCT_UNIT, product.getUnit());
		contentValues.put(PRODUCT_PIECES, product.getPieces());
		contentValues.put(PRODUCT_PIECES_AMOUNT, product.getPiecesAmount());
		contentValues.put(PRODUCT_TOTAL_AMOUNT, product.getTotalAmount());
		contentValues.put(PRODUCT_IN_STOCK_PIECES, product.getInStockPieces());
		contentValues.put(PRODUCT_IN_STOCK_AMOUNT, product.getInStockAmount());
		contentValues.put(PRODUCT_IMAGE_PATH, product.getImagePath());
		contentValues.put(PRODUCT_TOTAL_PIECES, product.getTotalPieces());
		
		if (!tempList) {
			database.insert(TABLE_NAME, null, contentValues);
		} else {
			database.insert(TABLE_NAME_TEMP, null, contentValues);
		}
	}
	
	public void clearTable(boolean tempList){
		String buildSQL = "DELETE FROM " + (tempList ? TABLE_NAME_TEMP : TABLE_NAME);
		Log.e("DATABASE CLEANING", "HEJ MED DIG");
		database.execSQL(buildSQL);
	}
	
	public void insertProducts(List<Product> products, boolean tempList) {
		for (Product p : products) {
			insertProduct(p, tempList);
		}
	}
	
	public Product getProduct(long id) {
		String buildSQL = "SELECT * FROM " + TABLE_NAME + " WHERE " + PRODUCT_ID + " = " + id;
		Cursor cursor = database.rawQuery(buildSQL, null);
		cursor.moveToFirst();
		return cursorToProduct(cursor);
	}
	
	public Product getProductByName(String name, boolean tempList) {
		String buildSQL = "SELECT * FROM " + (tempList ? TABLE_NAME_TEMP : TABLE_NAME) + " WHERE " + PRODUCT_NAME + " = '" + name + "'";
		Cursor cursor = database.rawQuery(buildSQL, null);
		if (cursor.getCount() > 0)
			cursor.moveToFirst();
		else
			return null;
		return cursorToProduct(cursor);
	}

	public List<Product> getProductsByCategory(String category) {
		List<Product> products = new ArrayList<Product>();
		String buildSQL = "SELECT * FROM " + TABLE_NAME + " WHERE " + PRODUCT_CATEGORY + " ='" + category.toUpperCase(Locale.US) + "'";
		Log.i(TAG, buildSQL);

		Cursor cursor = database.rawQuery(buildSQL, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Product p = cursorToProduct(cursor);
			products.add(p);
			cursor.moveToNext();
		}
		Log.d(TAG, "getProductsByCategory SQL: " + buildSQL);
		
		return products;
	}
	
	public List<Product> getAllTempProducts() {
		List<Product> products = new ArrayList<Product>();
		String buildSQL = "SELECT * FROM " + TABLE_NAME_TEMP;
		Log.i(TAG, buildSQL);

		Cursor cursor = database.rawQuery(buildSQL, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Product p = cursorToProduct(cursor);
			products.add(p);
			cursor.moveToNext();
		}
		Log.d(TAG, "getAllTempProducts SQL: " + buildSQL);
		
		return products;
	}
	
	public int getCountByCategory(String category) {
		List<Product> products = getProductsByCategory(category);
		return products.size();
	}
	
	public void updateProduct(Product product, boolean tempList) {		
		ContentValues contentValues = new ContentValues();
		contentValues.put(PRODUCT_NAME, product.getName());
		contentValues.put(PRODUCT_CATEGORY, product.getCategory().toUpperCase(Locale.US));
		contentValues.put(PRODUCT_UNIT, product.getUnit());
		contentValues.put(PRODUCT_PIECES, product.getPieces());
		contentValues.put(PRODUCT_PIECES_AMOUNT, product.getPiecesAmount());
		contentValues.put(PRODUCT_TOTAL_AMOUNT, product.getTotalAmount());
		contentValues.put(PRODUCT_IN_STOCK_PIECES, product.getInStockPieces());
		contentValues.put(PRODUCT_IN_STOCK_AMOUNT, product.getInStockAmount());
		contentValues.put(PRODUCT_IMAGE_PATH, product.getImagePath());
		contentValues.put(PRODUCT_TOTAL_PIECES, product.getTotalPieces());
		
		database.update((tempList ? TABLE_NAME_TEMP : TABLE_NAME), contentValues, PRODUCT_ID + "=?", new String[] {""+product.getId()});
	}
	
	public void updateProducts(List<Product> products, boolean tempList) {
		for (Product p : products) {
			updateProduct(p, tempList);
		}
	}
	
	public void deleteProduct(Product product, boolean tempList) {
		database.delete((tempList ? TABLE_NAME_TEMP : TABLE_NAME), PRODUCT_ID + "=?", new String[] {""+product.getId()});
	}

	private Product cursorToProduct(Cursor cursor) {
		Product p = new Product();
		p.setId(cursor.getLong(cursor.getColumnIndex(PRODUCT_ID)));
		p.setName(cursor.getString(cursor.getColumnIndex(PRODUCT_NAME)));
		p.setCategory(cursor.getString(cursor.getColumnIndex(PRODUCT_CATEGORY)));
		p.setUnit(cursor.getString(cursor.getColumnIndex(PRODUCT_UNIT)));
		p.setPieces(cursor.getLong(cursor.getColumnIndex(PRODUCT_PIECES)));
		p.setPiecesAmount(cursor.getDouble(cursor.getColumnIndex(PRODUCT_PIECES_AMOUNT)));
		p.setTotalAmount(cursor.getDouble(cursor.getColumnIndex(PRODUCT_TOTAL_AMOUNT)));		
		p.setInStockAmount(cursor.getDouble(cursor.getColumnIndex(PRODUCT_IN_STOCK_AMOUNT)));
		p.setInStockPieces(cursor.getDouble(cursor.getColumnIndex(PRODUCT_IN_STOCK_PIECES)));
		p.setImagePath(cursor.getString(cursor.getColumnIndex(PRODUCT_IMAGE_PATH)));
		p.setTotalPieces(cursor.getLong(cursor.getColumnIndex(PRODUCT_TOTAL_PIECES)));
		return p;
	}

	/**
	 * this DatabaseOpenHelper class will actually be used to perform database related operation 
	 */
	private class DatabaseOpenHelper extends SQLiteOpenHelper {

		public DatabaseOpenHelper(Context aContext) {
			super(aContext, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase sqLiteDatabase) {
		
			String buildSQL = "CREATE TABLE " + TABLE_NAME + "( " + 
					PRODUCT_ID + " INTEGER PRIMARY KEY, " +
					PRODUCT_NAME + " TEXT, "  +
					PRODUCT_CATEGORY + " TEXT, "  +
					PRODUCT_UNIT + " TEXT, " + 
					PRODUCT_PIECES + " REAL, " + 
					PRODUCT_TOTAL_PIECES + " REAL, " + 
					PRODUCT_PIECES_AMOUNT + " REAL, " + 
					PRODUCT_TOTAL_AMOUNT + " REAL, " + 
					PRODUCT_IN_STOCK_AMOUNT + " REAL, " + 
					PRODUCT_IN_STOCK_PIECES + " REAL, " + 
					PRODUCT_IMAGE_PATH + " TEXT )";

			Log.d(TAG, "onCreate SQL product table: " + buildSQL);
			sqLiteDatabase.execSQL(buildSQL);
			
			buildSQL = "CREATE TABLE " + TABLE_NAME_TEMP + "( " + 
					PRODUCT_ID + " INTEGER PRIMARY KEY, " +
					PRODUCT_NAME + " TEXT, "  +
					PRODUCT_CATEGORY + " TEXT, "  +
					PRODUCT_UNIT + " TEXT, " + 
					PRODUCT_PIECES + " REAL, " +
					PRODUCT_TOTAL_PIECES + " REAL, " + 
					PRODUCT_PIECES_AMOUNT + " REAL, " + 
					PRODUCT_TOTAL_AMOUNT + " REAL, " + 
					PRODUCT_IN_STOCK_AMOUNT + " REAL, " + 
					PRODUCT_IN_STOCK_PIECES + " REAL, " + 
					PRODUCT_IMAGE_PATH + " TEXT )";

			Log.d(TAG, "onCreate SQL product temp table: " + buildSQL);
			sqLiteDatabase.execSQL(buildSQL);
		}

		@Override
		public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
			
			String buildSQL = "DROP TABLE IF EXISTS " + TABLE_NAME;
			Log.d(TAG, "onUpgrade SQL: " + buildSQL);
			sqLiteDatabase.execSQL(buildSQL);	// drop previous table
			
			buildSQL = "DROP TABLE IF EXISTS " + TABLE_NAME_TEMP;
			Log.d(TAG, "onUpgrade SQL: " + buildSQL);
			sqLiteDatabase.execSQL(buildSQL);	// drop previous table
			
			onCreate(sqLiteDatabase);	// create the table from the beginning
		}
		
	}
}
